const express = require('express');
const router = express.Router();
const { query } = require('../database/db');

const checkPermission = require('../middleware/checkPermission');
const auth = require('../middleware/auth');

// 获取所有班级列表（带专业信息和学生数量）
router.get('/', async (req, res) => {
    try {
        const { name, program_id, status, page_size = 10, page = 1 } = req.query;
        
        // 基础SQL查询（优化JOIN方式）
        let sql = `
            SELECT 
                c.id, 
                c.name, 
                c.class_id, 
                c.created_at,
                c.state,
                p.name AS program_name,
                COUNT(DISTINCT s.id) AS student_count,
                u.id AS teacher_id,
                u.truename AS teacher_name
            FROM classes c
            JOIN programs p ON c.program_id = p.id
            LEFT JOIN users u ON u.class_id = c.id AND u.role_id IN (2,7) AND u.state = 0
            LEFT JOIN students s ON s.class_id = c.id AND s.state = 0
        `;
        
        let countSql = `SELECT COUNT(*) as total FROM classes c`;
        let conditions = [];
        let params = [];
        
        // 添加筛选条件
        if (name) {
            conditions.push(`c.name LIKE ?`);
            params.push(`%${name}%`);
        }
        if (program_id) {
            conditions.push(`c.program_id = ?`);
            params.push(program_id);
        }
        if (status !== undefined) {
            conditions.push(`c.state = ?`);
            params.push(status);
        }
        
        // 添加WHERE子句
        if (conditions.length > 0) {
            const whereClause = ` WHERE ${conditions.join(' AND ')}`;
            sql += whereClause;
            countSql += whereClause;
        }
        
        // 添加GROUP BY和分页
        sql += ` GROUP BY c.id, u.id, u.truename 
                ORDER BY c.created_at DESC 
                LIMIT ? OFFSET ?`;
        params.push(parseInt(page_size), (parseInt(page) - 1) * parseInt(page_size));
        
        // 修正总数查询
        countSql = `SELECT COUNT(*) as total FROM classes c 
                   ${conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : ''}`;
        
        // 执行查询
        const [rawClasses, totalResult] = await Promise.all([
            query(sql, params),
            query(countSql, params.slice(0, -2))
        ]);
        
        res.json({
            code: 200,
            data: {
                list: rawClasses,
                total: totalResult[0].total,
                page: parseInt(page),
                page_size: parseInt(page_size)
            }
        });
    } catch (error) {
        console.error('获取班级列表失败:', error);
        res.status(500).json({ code: 500, msg: '获取班级列表失败' });
    }
});

// 创建新班级
router.post('/add', async (req, res) => {
    const { program_id, name, class_id } = req.body;

    // 验证参数
    if (!program_id || !name || !class_id) {
        return res.status(400).json({ code: 400, msg: '缺少必要参数: program_id, name 或 class_id' });
    }

    try {
        // 检查班级名称是否已存在
        const checkNameSql = 'SELECT id FROM classes WHERE name = ?';
        const nameExists = await query(checkNameSql, [name]);
        if (nameExists.length > 0) {
            return res.status(400).json({ code: 400, msg: '班级名称已存在' });
        }

        // 检查专业是否存在
        const checkProgramSql = 'SELECT id FROM programs WHERE id = ?';
        const programExists = await query(checkProgramSql, [program_id]);
        if (programExists.length === 0) {
            return res.status(400).json({ code: 400, msg: '专业不存在' });
        }

        // 创建班级
        const insertSql = 'INSERT INTO classes (program_id, name, class_id) VALUES (?, ?, ?)';
        const result = await query(insertSql, [program_id, name, class_id]);

        res.json({
            code: 200,
            msg: '创建成功',
            data: { id: result.insertId }
        });
    } catch (error) {
        console.error('创建班级失败:', error);
        res.status(500).json({ code: 500, msg: '创建班级失败' });
    }
});

// 获取班级详情
router.get('/get/:id', async (req, res) => {
    const { id } = req.params;

    try {
        const sql = `
            SELECT 
                c.id, 
                c.name, 
                c.class_id, 
                c.program_id,
                c.created_at,
                p.name AS program_name
            FROM classes c
            JOIN programs p ON c.program_id = p.id
            WHERE c.id = ?
        `;
        const results = await query(sql, [id]);

        if (results.length === 0) {
            return res.status(404).json({ code: 404, msg: '班级不存在' });
        }

        // 获取班级学生列表
        // const studentsSql = `
        //     SELECT 
        //         u.id, 
        //         u.username, 
        //         u.truename, 
        //         u.email, 
        //         u.phone,
        //         r.name AS role_name
        //     FROM students u
        //     JOIN roles r ON u.role_id = r.id
        //     WHERE u.class_id = ? AND u.state = 0
        //     ORDER BY u.truename ASC
        // `;
        // const students = await query(studentsSql, [id]);

        res.json({
            code: 200,
            data: {
                ...results[0],
                // students
            }
        });
    } catch (error) {
        console.error('获取班级详情失败:', error);
        res.status(500).json({ code: 500, msg: '获取班级详情失败' });
    }
});

// 更新班级信息
router.put('/update/:id', async (req, res) => {
    const { id } = req.params;
    const { program_id, name, class_id } = req.body;

    // 验证参数
    if (!program_id || !name || !class_id) {
        return res.status(400).json({ code: 400, msg: '缺少必要参数: program_id, name 或 class_id' });
    }

    try {
        // 检查班级是否存在
        const checkClassSql = 'SELECT id FROM classes WHERE id = ?';
        const classExists = await query(checkClassSql, [id]);
        if (classExists.length === 0) {
            return res.status(404).json({ code: 404, msg: '班级不存在' });
        }

        // 检查班级名称是否已被其他班级使用
        const checkNameSql = 'SELECT id FROM classes WHERE name = ? AND id != ?';
        const nameExists = await query(checkNameSql, [name, id]);
        if (nameExists.length > 0) {
            return res.status(400).json({ code: 400, msg: '班级名称已被其他班级使用' });
        }

        // 检查专业是否存在
        const checkProgramSql = 'SELECT id FROM programs WHERE id = ?';
        const programExists = await query(checkProgramSql, [program_id]);
        if (programExists.length === 0) {
            return res.status(400).json({ code: 400, msg: '专业不存在' });
        }

        // 更新班级信息
        const updateSql = 'UPDATE classes SET program_id = ?, name = ?, class_id = ? WHERE id = ?';
        await query(updateSql, [program_id, name, class_id, id]);

        res.json({ code: 200, msg: '更新成功' });
    } catch (error) {
        console.error('更新班级失败:', error);
        res.status(500).json({ code: 500, msg: '更新班级失败' });
    }
});

// 启用班级
router.put('/:id/enable', async (req, res) => {
    const { id } = req.params;

    try {
        // 检查班级是否存在
        const checkClassSql = 'SELECT id FROM classes WHERE id = ?';
        const classExists = await query(checkClassSql, [id]);
        if (classExists.length === 0) {
            return res.status(404).json({ code: 404, msg: '班级不存在' });
        }

        // 启用班级
        const enableSql = 'UPDATE classes SET state = 0 WHERE id = ?';
        await query(enableSql, [id]);

        res.json({ code: 200, msg: '启用班级成功' });
    } catch (error) {
        console.error('启用班级失败:', error);
        res.status(500).json({ code: 500, msg: '启用班级失败' });
    }
});

// 禁用班级
router.put('/:id/disable', async (req, res) => {
    const { id } = req.params;

    try {
        // 检查班级是否存在
        const checkClassSql = 'SELECT id FROM classes WHERE id = ?';
        const classExists = await query(checkClassSql, [id]);
        if (classExists.length === 0) {
            return res.status(404).json({ code: 404, msg: '班级不存在' });
        }

        // 禁用班级
        const disableSql = 'UPDATE classes SET state = 1 WHERE id = ?';
        await query(disableSql, [id]);

        res.json({ code: 200, msg: '禁用班级成功' });
    } catch (error) {
        console.error('禁用班级失败:', error);
        res.status(500).json({ code: 500, msg: '禁用班级失败' });
    }
});

// 修改后的删除班级逻辑（考虑status状态）
router.delete('/:id', async (req, res) => {
    const { id } = req.params;

    try {
        // 检查班级是否存在
        const checkClassSql = 'SELECT id, state FROM classes WHERE id = ?';
        const classInfo = await query(checkClassSql, [id]);
        if (classInfo.length === 0) {
            return res.status(404).json({ code: 404, msg: '班级不存在' });
        }

        // 如果班级已被禁用，直接删除
        if (classInfo[0].state === 1) {
            const deleteSql = 'DELETE FROM classes WHERE id = ?';
            await query(deleteSql, [id]);
            return res.json({ code: 200, msg: '删除成功' });
        }

        // 检查班级是否有学生
        const checkStudentsSql = 'SELECT id FROM students WHERE class_id = ? AND state = 0 LIMIT 1';
        const hasStudents = await query(checkStudentsSql, [id]);
        if (hasStudents.length > 0) {
            return res.status(400).json({
                code: 400,
                msg: '班级下有学生，无法删除。请先禁用班级或转移学生后再删除。'
            });
        }

        // 删除班级
        const deleteSql = 'DELETE FROM classes WHERE id = ?';
        await query(deleteSql, [id]);

        res.json({ code: 200, msg: '删除成功' });
    } catch (error) {
        console.error('删除班级失败:', error);
        res.status(500).json({ code: 500, msg: '删除班级失败' });
    }
});

// 获取班级学生列表
router.get('/:id/students', async (req, res) => {
    const { id } = req.params;

    try {
        // 检查班级是否存在
        const checkClassSql = 'SELECT id FROM classes WHERE id = ?';
        const classExists = await query(checkClassSql, [id]);
        if (classExists.length === 0) {
            return res.status(404).json({ code: 404, msg: '班级不存在' });
        }

        // 获取学生列表
        const sql = `
            SELECT 
                s.id, 
                s.name, 
                s.gender, 
                s.email, 
                s.phone,
                s.student_id
            FROM students s
            WHERE s.class_id = ?
            ORDER BY s.id DESC
        `;
        const students = await query(sql, [id]);

        res.json({ code: 200, data: students });
    } catch (error) {
        console.error('获取班级学生列表失败:', error);
        res.status(500).json({ code: 500, msg: '获取班级学生列表失败' });
    }
});

// 获取所有学生列表（带分页）
router.get('/students', async (req, res) => {
    try {
        const {
            name,
            class_id,
            student_id,
            gender,
            page_size = 10,
            page = 1
        } = req.query;

        // 基础SQL查询
        let sql = `
            SELECT 
                s.id,
                s.name,
                s.gender,
                s.email,
                s.phone,
                s.student_id,
                s.class_id,
                c.name AS class_name,
                p.name AS program_name
            FROM students s
            LEFT JOIN classes c ON s.class_id = c.id
            LEFT JOIN programs p ON c.program_id = p.id
        `;

        // 总数查询SQL
        let countSql = `SELECT COUNT(*) as total FROM students s`;

        let conditions = ['s.state = 0', 's.class_id = NULL']; // 只查询未删除的学生
        let params = [];

        // 添加筛选条件
        if (name) {
            conditions.push(`s.name LIKE ?`);
            params.push(`%${name}%`);
        }
        if (class_id) {
            conditions.push(`s.class_id = ?`);
            params.push(class_id);
        }
        if (student_id) {
            conditions.push(`s.student_id LIKE ?`);
            params.push(`%${student_id}%`);
        }
        if (gender) {
            conditions.push(`s.gender = ?`);
            params.push(gender);
        }

        // 添加WHERE子句
        if (conditions.length > 0) {
            const whereClause = ` WHERE ${conditions.join(' AND ')}`;
            sql += whereClause;
            countSql += whereClause;
        }

        // 添加排序和分页
        sql += ` ORDER BY s.id DESC LIMIT ? OFFSET ?`;
        params.push(parseInt(page_size), (parseInt(page) - 1) * parseInt(page_size));

        // 执行查询
        const [students, totalResult] = await Promise.all([
            query(sql, params),
            query(countSql, params.slice(0, -2)) // 移除分页参数用于总数查询
        ]);

        res.json({
            code: 200,
            data: {
                list: students,
                total: totalResult[0].total,
                page: parseInt(page),
                page_size: parseInt(page_size)
            }
        });
    } catch (error) {
        console.error('获取学生列表失败:', error);
        res.status(500).json({ code: 500, msg: '获取学生列表失败' });
    }
});

// 将学生分配到班级
router.post('/:id/students', async (req, res) => {
    const { id: class_id } = req.params;
    const { student_ids } = req.body;

    // 验证参数
    if (!student_ids || !Array.isArray(student_ids) || student_ids.length === 0) {
        return res.status(400).json({ code: 400, msg: '请提供学生ID数组' });
    }

    try {
        // 检查班级是否存在
        const checkClassSql = 'SELECT id FROM classes WHERE id = ?';
        const classExists = await query(checkClassSql, [class_id]);
        if (classExists.length === 0) {
            return res.status(404).json({ code: 404, msg: '班级不存在' });
        }

        // 检查所有学生是否存在
        const checkStudentsSql = `SELECT id FROM students WHERE id IN (?) AND state = 0`;
        const students = await query(checkStudentsSql, [student_ids]);
        if (students.length !== student_ids.length) {
            return res.status(400).json({
                code: 400,
                msg: '部分学生不存在或已被禁用'
            });
        }

        // 更新学生的班级ID
        const updateSql = 'UPDATE students SET class_id = ? WHERE id IN (?)';
        await query(updateSql, [class_id, student_ids]);

        res.json({ code: 200, msg: '分配学生成功' });
    } catch (error) {
        console.error('分配学生到班级失败:', error);
        res.status(500).json({ code: 500, msg: '分配学生到班级失败' });
    }
});

// 从班级移除学生
router.delete('/:id/students/:student_id', async (req, res) => {
    const { id: class_id, student_id } = req.params;

    try {
        // 检查班级是否存在
        const checkClassSql = 'SELECT id FROM classes WHERE id = ?';
        const classExists = await query(checkClassSql, [class_id]);
        if (classExists.length === 0) {
            return res.status(404).json({ code: 404, msg: '班级不存在' });
        }

        // 检查学生是否存在且属于该班级
        const checkStudentSql = 'SELECT id FROM students WHERE id = ? AND class_id = ? AND state = 0';
        const studentExists = await query(checkStudentSql, [student_id, class_id]);
        if (studentExists.length === 0) {
            return res.status(404).json({
                code: 404,
                msg: '学生不存在或不属于该班级'
            });
        }

        // 将学生的班级ID设为NULL
        const updateSql = 'UPDATE students SET class_id = NULL WHERE id = ?';
        await query(updateSql, [student_id]);

        res.json({ code: 200, msg: '移除学生成功' });
    } catch (error) {
        console.error('从班级移除学生失败:', error);
        res.status(500).json({ code: 500, msg: '从班级移除学生失败' });
    }
});

// 给班级设置老师
router.post('/setTeacher', async (req, res) => {
    const { class_id, teacher_id } = req.body;

    // 验证参数
    if (!class_id || !teacher_id) {
        return res.status(400).json({
            code: 400,
            msg: '缺少必要参数: class_id 或 teacher_id'
        });
    }

    try {
        // 检查班级是否存在
        const checkClassSql = 'SELECT id FROM classes WHERE id = ?';
        const classExists = await query(checkClassSql, [class_id]);
        if (classExists.length === 0) {
            return res.status(404).json({
                code: 404,
                msg: '班级不存在'
            });
        }

        // 检查用户是否存在且是老师(role_id=7)
        const checkTeacherSql = 'SELECT id FROM users WHERE id = ? AND role_id IN (2,7)';
        const teacherExists = await query(checkTeacherSql, [teacher_id]);
        if (teacherExists.length === 0) {
            return res.status(404).json({
                code: 404,
                msg: '用户不存在或不是老师(role_id=7/2)'
            });
        }

        // 更新老师的class_id
        const updateSql = 'UPDATE users SET class_id = ? WHERE id = ?';
        await query(updateSql, [class_id, teacher_id]);

        console.log('更新语句：：',updateSql,class_id, teacher_id)

        res.json({
            code: 200,
            msg: '设置老师成功',
            data: {
                class_id,
                teacher_id
            }
        });
    } catch (error) {
        console.error('设置老师失败:', error);
        res.status(500).json({
            code: 500,
            msg: '设置老师失败'
        });
    }
});

// 获取所有专业列表（用于前端下拉选择）
router.get('/programs/list', async (req, res) => {
    try {
        const sql = 'SELECT id, name FROM programs ORDER BY name ASC';
        const programs = await query(sql);
        res.json({ code: 200, data: programs });
    } catch (error) {
        console.error('获取专业列表失败:', error);
        res.status(500).json({ code: 500, msg: '获取专业列表失败' });
    }
});

// 获取所有班级列表（用于前端下拉选择）
router.get('/classes/list', async (req, res) => {
    try {
        const sql = 'SELECT id, name FROM classes ORDER BY name ASC';
        const programs = await query(sql);
        res.json({ code: 200, data: programs });
    } catch (error) {
        console.error('获取专业列表失败:', error);
        res.status(500).json({ code: 500, msg: '获取专业列表失败' });
    }
});



module.exports = router;